﻿CREATE PROCEDURE [dbo].[s_PaymentInput_Process]
	@PaymentInputID int
AS 
SET NOCOUNT ON
PRINT OBJECT_NAME(@@PROCID)+':Processing - '+STR(@PaymentInputID)

DECLARE @LoanID int,@NewPayment money, @DatePaid smalldatetime,@StatusID int,@PaymentID int,@WaterFallID int
DECLARE @PrincipalPart numeric(18,2),@InterestPart numeric(18,2),@LateFeePart numeric(18,2),@NSFFeePart numeric(18,2)

SELECT @LoanID = PINP.LoanID ,@NewPayment = PINP.Amount, @DatePaid = PINP.DatePaid,
@StatusID = PINP.StatusID,@PaymentID = PINP.PaymentID,@WaterFallID = PINP.WaterFallID,
@PrincipalPart = PINP.PrincipalPart,@InterestPart = PINP.InterestPart,@LateFeePart = PINP.LateFeePart,@NSFFeePart = PINP.NSFFeePart
FROM t_PaymentInput PINP
WHERE PINP.ID = @PaymentInputID

DECLARE @RecalcPrinBalParamID int SET @RecalcPrinBalParamID = dbo.fs_StateParamID__RecalcPrinBal()
DELETE FROM t_PaymentTransaction WHERE PaymentID = @PaymentID
DECLARE @PrincBal money,@DateNext smalldatetime,@PrevDatePaid smalldatetime, @PaymentNumber numeric(5,2),@PrevPaymentID int

SELECT TOP 1 @PrincBal = P.PrincipalBalance,@DateNext = P.DateNext
						,@PrevDatePaid = PINP.DatePaid, @PaymentNumber = PaymentNumber
						,@PrevPaymentID = P.ID
FROM t_Payment P INNER JOIN t_PaymentInput PINP ON P.ID = PINP.PaymentID
WHERE P.LoanID = @LoanID AND (PINP.DatePaid < @DatePaid OR PINP.DatePaid = @DatePaid AND PINP.ID < @PaymentInputID) AND PINP.WaterFallID > 0
ORDER BY PINP.DatePaid DESC, PINP.ID DESC

--SELECT  @PaymentInputID PaymentInputID,@PrevPaymentID PrevPaymentID, @PrincBal PrevBalance,@DateNext  PrevDateNext,@PrevDatePaid  PrevDatePaid, @PaymentNumber PrevPaymentNumner

DECLARE @Rate numeric(9,9),@ToLateFee money,@FirstPmtDate datetime,@Payment money,
				@APR numeric(10,10),@DayCountConvention tinyint,@GraceDays int,
				@NewDateNext smalldatetime,@ExpectedBalance money,@ExpectedPayment smallint
SELECT @APR = LI.APR/100.00000000,
			 @Rate = LI.APR/LI.PaymentsPerYear/100,
			 @PrincBal = ISNULL(@PrincBal,LI.AmountFinanced),
			 @FirstPmtDate = LI.FirstPaymentDate,
			 @DateNext = ISNULL(@DateNext,LI.FirstPaymentDate),
			 @PrevDatePaid = ISNULL(@PrevDatePaid,LI.ContractDate),
			 @Payment = LI.Payment,
			 @DayCountConvention = LI.DayCountConventionID,
			 @PaymentNumber = ISNULL(@PaymentNumber,0)
FROM v_TruthInLending LI WHERE @LoanID = LI.LoanID

SELECT @ExpectedBalance = RemainingPrinciple, @ExpectedPayment = PaymentNumber
FROM PaymentScheduleByLoanID(@LoanID) WHERE Date <= @DateNext ORDER BY PaymentNumber

IF ISNULL(@WaterFallID,1) > 0 BEGIN --Principal Balance Transaction
SELECT			 @ToLateFee = LCI.Charge,@GraceDays = ISNULL(SLC.[Late Days],PS.LateFeeGrace)
FROM v_TruthInLending LI
INNER JOIN t_Loan_Borrower L_B ON L_B.LoanID = LI.LoanID AND L_B.Pos = 0
LEFT OUTER JOIN t_StateLateCharge SLC ON SLC.State = LI.LoanState
INNER JOIN ts_LoanPaymentType LPT ON LI.PaymentTypeID = LPT.ID
CROSS JOIN t_PaymentSetting PS
CROSS APPLY LateChargeInfo(LI.PrincipalBalance,LI.Payment,LI.PaymentsPerYear,@DateNext,@DatePaid,
	ISNULL(SLC.[Late Days],PS.LateFeeGrace),ISNULL(SLC.[Late Chg%],PS.LateFeePerc),ISNULL(SLC.[Late Chg Max],PS.LateFeeMax))LCI
WHERE LI.LoanID = @LoanID

DECLARE @ToInterest money
SET @ToInterest = CASE WHEN NOT @InterestPart IS NULL THEN @InterestPart
											 ELSE dbo.ACCRINTM(@PrevDatePaid,@DatePaid,@APR,@PrincBal,@DayCountConvention) END
PRINT '@ToInterest:'+str(@ToInterest)+':'+STR(dbo.ACCRINTM(@PrevDatePaid,@DatePaid,@APR,@PrincBal,@DayCountConvention))

--SELECT 'PEB'T,* FROM PaymentEscrowBalance(@LoanID)
DECLARE @OwnedInterest money,@OwnedLateFee money,@OwnedNSF money
SELECT @OwnedInterest = 0,@OwnedLateFee  = 0,@OwnedNSF = 0
SELECT @OwnedInterest = Interest,@OwnedLateFee = LateFee,@OwnedNSF = NSF FROM PaymentEscrowBalance(@LoanID,@DatePaid)
--SELECT @OwnedInterest OwnedInterest,@OwnedLateFee OwnedLateFee,@OwnedNSF OwnedNSF

DECLARE @WF TABLE(ToOwnedInterest money,InterestPart money,AddEscrowInterest money,PrincipalPart money,ToOwnedLateFee money,
									LateFeePart money,AddEscrowLateFee money,ToOwnedNSF money,NSFPart money,AddEscrowNSF money,TranBalance money)

INSERT INTO @WF
SELECT WF.* 
FROM WaterFall (@WaterFallID,@Payment,@NewPayment-ISNULL(@LateFeePart,0),@ToInterest,
								CASE WHEN @LateFeePart IS NULL THEN @ToLateFee ELSE 0 END,0,@OwnedInterest,
								@OwnedLateFee,@OwnedNSF) WF
IF NOT @LateFeePart IS NULL
	UPDATE @WF SET LateFeePart = @LateFeePart,AddEscrowLateFee = AddEscrowLateFee+(@LateFeePart-@ToLateFee)
--SELECT 'WF' T,* FROM @WF

DECLARE @PrincBalNew money SELECT @PrincBalNew = @PrincBal - (WF.PrincipalPart + WF.TranBalance) FROM @WF WF

SELECT @PaymentNumber = PS.PaymentNumberReal
FROM PaymentScheduleCurrentByPrincipalBalance(@LoanID,@PrincBalNew)PS
--SELECT @PaymentNumber = @PaymentNumber + @NewPayment/ @Payment

PRINT dbo.sprint1('@PaymentNumber:%s',@PaymentNumber)

SELECT @NewDateNext = Date FROM PaymentScheduleByLoanID(@LoanID) CROSS JOIN t_PaymentSetting PS
WHERE PaymentNumber = FLOOR(@PaymentNumber+PS.AmountTolerance)

PRINT dbo.sprint1('@LateFeePart:%s',@LateFeePart)

UPDATE P SET
	P.DateScheduled = @DateNext,
	P.DateNext = @NewDateNext,
	P.PrincipalPart = WF.PrincipalPart + WF.TranBalance,
	P.InterestPart = WF.InterestPart/*@ToInterest + WF.AddEscrowInterest*/,
	P.LateFeePart = CASE WHEN @LateFeePart > 0 THEN @LateFeePart ELSE WF.LateFeePart END,
	P.NSFFeePart = CASE WHEN @NSFFeePart > 0 THEN @NSFFeePart ELSE 0 END,
	P.PrincipalBalance = @PrincBalNew,
	P.PaymentNumber = @PaymentNumber,
	P.ExpectedBalance = @ExpectedBalance,
	P.ExpectedPayment = @ExpectedPayment,P.DayCountConventionID = @DayCountConvention,P.IsProcessed = 1
FROM t_Payment P CROSS JOIN @WF WF
WHERE P.ID = @PaymentID

INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
SELECT @PaymentID,WF.ToOwnedInterest,WF.ToOwnedLateFee,WF.ToOwnedNSF FROM @WF WF

INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
SELECT @PaymentID,WF.AddEscrowInterest,WF.AddEscrowLateFee,WF.AddEscrowNSF FROM @WF WF

--SELECT 'P'P,* FROM t_Payment WHERE LoanID = @LoanID-- ID = (SELECT ID FROM @O)
--SELECT 'PT'PT,PT.* FROM v_PaymentTransaction PT WHERE PT.PaymentID = @PaymentID
END
IF @StatusID = dbo.fs_StateID__Payments__CheckNSF() BEGIN
	DECLARE @NSFFee numeric(9,2) SET @NSFFee = 25
	UPDATE t_Payment SET 
				PrincipalBalance = @PrincBal,
				ExpectedBalance = @ExpectedBalance,
				ExpectedPayment=0,
				PaymentNumber = 0,
				InterestPart = 0,LateFeePart = 0,
			  NSFFeePart = 0,PrincipalPart = 0
	WHERE ID = @PaymentID

	INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
	VALUES(@PaymentID,0,0,-@NSFFee)
END

--SELECT @PrincBal PrincBal,@DateNext DateNext,@DatePaid DatePaid,@PrevDatePaid PrevDatePaid,@ToInterest ToInterest,
--			 @NewDateNext NewDateNext,@ToLateFee LateCharges,@PaymentNumber Pmt#

